﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace QLKhachSan.DAO
{
    public class clsKetNoi
    {

        public static DataTable Load(string sql)  //select ko tham số
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["QLKhachSan.Properties.Settings.kn"].ConnectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            cmd.Dispose();
            da.Dispose();
            dt.Dispose();
            return dt;
        }
        public static DataTable LoadCoTS(string sql, string[] name, object[] value, int SoThamSo)  //select có tham số
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["QLKhachSan.Properties.Settings.kn"].ConnectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < SoThamSo; i++)
            {
                cmd.Parameters.AddWithValue(name[i], value[i]);
            }
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            cmd.Dispose();
            da.Dispose();
            dt.Dispose();
            return dt;
        }
        public static void ThucThiLenh(string sql)  //thực thi lệnh ko có tham số
        {
            //
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["QLKhachSan.Properties.Settings.kn"].ConnectionString);
            con.Open();
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();
            con.Close();
            cmd.Dispose();
        }
        public static void ThucThiLenhCoTS(string sql, string[] name, object[] value, int SoThamSo)  //thực thi lệnh có tham số
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["QLKhachSan.Properties.Settings.kn"].ConnectionString);
            con.Open();
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < SoThamSo; i++)
            {
                cmd.Parameters.AddWithValue(name[i], value[i]);
            }
            cmd.ExecuteNonQuery();
            con.Close();
            cmd.Dispose();
        }

        public static SqlDataReader LenhRead(string sql) // lệnh reader ko tham số(stor select ko điều kiện)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["QLKhachSan.Properties.Settings.kn"].ConnectionString);
            con.Open();
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr = cmd.ExecuteReader(); ;
            return dr;
        }
        public static SqlDataReader LenhReadCoTS(string sql, string[] name, object[] value, int SoThamSo) // lệnh read có tham số
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["QLKhachSan.Properties.Settings.kn"].ConnectionString);
            con.Open();
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < SoThamSo; i++)
            {
                cmd.Parameters.AddWithValue(name[i], value[i]);
            }
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }

        public static List<string> GetCombo(string sql, string row)   //gọi dữ liệu lên conbobox loại bỏ giá trị trùng lặp
        {
            List<string> lis = new List<string>();
            DataTable dt = clsKetNoi.Load(sql);
            if (dt == null)
            {
                lis = null;
            }
            else
            {
                int r = dt.Rows.Count;
                if (r > 0)
                {
                    foreach (DataRow i in dt.Rows)
                    {
                        if (!lis.Contains(i[row].ToString()))
                        {
                            lis.Add(i[row].ToString());
                        }
                    }
                }
            }
            return lis;
        }
        public static DataView TimKiem(string sql, string row, string giatri)
        {

            DataView dv = new DataView(clsKetNoi.Load(sql));
            dv.RowFilter = row + " like '%" + giatri + "%'";
            return dv;
        }        

        public static DataView TimKiemKhoang(string sql, string row, string tu, string den)
        {
            DataView dv = new DataView(clsKetNoi.Load(sql));
            dv.RowFilter = row + " >='" + tu + "' AND " + row + " <= '" + den + "'";
            return dv;
        }


    }
}
